package com.kang.until;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.kang.pojo.PurchaseOrderItem;


public class purchaseOrderExcelInput extends HttpServlet {

	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;

	public void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {

		doPost(request, response);
	}

	public void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
	         //请求发送都是utf-8
		     request.setCharacterEncoding("UTF-8");
	    	 response.setCharacterEncoding("UTF-8");
	         //文件路径
//	     	List<PurchaseOrderItem> list = parseExcel("C:\\Users\\zs\\Desktop\\采购申请.xlsx");
//	    	
//			for (int i = 0; i < list.size(); i++) {
//
//				System.out.println("===list==="+ list.get(i));
//				
//			}
	}
	
	// 解析Excel,读取内容,path Excel路径
	public static List<PurchaseOrderItem> parseExcel(String path) {
		List<PurchaseOrderItem> list = new ArrayList<PurchaseOrderItem>();
		File file = null;
		InputStream input = null;
		if (path != null && path.length() > 7) {
			// 判断文件是否是Excel(2003、2007)
			String suffix = path
					.substring(path.lastIndexOf("."), path.length());
			file = new File(path);
			try {
				input = new FileInputStream(file);
			} catch (FileNotFoundException e) {
				System.out.println("未找到指定的文件！");
			}
			// Excel2003
			if (".xls".equals(suffix)) {
				POIFSFileSystem fileSystem = null;
				// 工作簿
				HSSFWorkbook workBook = null;
				try {
					fileSystem = new POIFSFileSystem(input);
					workBook = new HSSFWorkbook(fileSystem);
				} catch (IOException e) {
					e.printStackTrace();
				}
				// 获取第一个工作簿
				HSSFSheet sheet = workBook.getSheetAt(0);
				list = getContent((Sheet) sheet);
				// Excel2007
			} else if (".xlsx".equals(suffix)) {
				XSSFWorkbook workBook = null;
				try {
					workBook = new XSSFWorkbook(input);
				} catch (IOException e) {
					e.printStackTrace();
				}
				// 获取第一个工作簿
				XSSFSheet sheet = workBook.getSheetAt(0);
				list = getContent((Sheet) sheet);
			}
		} else {
			System.out.println("非法的文件路径!");
		}
		return list;
	}

	public static List<PurchaseOrderItem> getContent(Sheet sheet) {
		
		List<PurchaseOrderItem> list = new ArrayList<PurchaseOrderItem>();
		// Excel数据总行数
		int rowCount = sheet.getPhysicalNumberOfRows();
		// 遍历数据行，略过标题行，从第二行开始
		for (int i = 1; i < rowCount; i++) {
			PurchaseOrderItem poi = new PurchaseOrderItem();
			Row row = sheet.getRow(i);
			int cellCount = row.getPhysicalNumberOfCells();
			// 遍历行单元格
			for (int j = 0; j < cellCount; j++) {
				Cell cell = row.getCell(j);
				switch (j) {
				case 0:
					// 根据cell中的类型来输出数据
					if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
						poi.setId((int)cell.getNumericCellValue());
					}
					break;
				case 1:
					if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
						poi.setPurchaseOrderID((int)cell.getNumericCellValue());
					}
					break;
				case 2:
					if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
						poi.setItemInformationID((int)cell.getNumericCellValue());
					}
					break;
				case 3:
					if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
						poi.setNumber((int)cell.getNumericCellValue());
					}
					break;
				case 4:
					if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
						poi.setSourceID((int)cell.getNumericCellValue());
					}
					break;
				case 5:
					if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
						poi.setStatus(cell.getStringCellValue());
					}
					break;
				case 6:
					if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
						poi.setItemCode(cell.getStringCellValue());
					}
					break;
				case 7:
					if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
						poi.setArticleBarCode(cell.getStringCellValue());
					}
					break;
				case 8:
					if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
						poi.setChineseName(cell.getStringCellValue());
					}
					break;
				case 9:
					if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
						poi.setPurchasingUnit(cell.getStringCellValue());
					}
					break;
				case 10:
					if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
						poi.setPurchaseSpecifications(cell.getStringCellValue());
					}
					break;
				case 11:
					if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
						poi.setSalesSpecifications(cell.getStringCellValue());
					}
					break;
				case 12:
					if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
						poi.setCategoryName(cell.getStringCellValue());
					}
					break;
				case 13:
					if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
						poi.setMaterialMaterial(cell.getStringCellValue());
					}
					break;
				case 14:
					if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
						poi.setBrand(cell.getStringCellValue());
					}
					break;
				}
			}
			list.add(poi);
		}
		return list;
	}

	
}
